﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace MINERP.DAL.Statistics
{
    public class InnerStatDAL : DALBase
    {
        public List<users> GetUsers()
        {
            return DataEntites.users.Where(s=>s.IsUseful).ToList();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="inType">0日统计1月统计</param>
        /// <param name="inStartTime">开始时间</param>
        /// <param name="inEndTime">结束时间</param>
        /// <param name="inUserId">反单责任人</param>
        /// <param name="inFromID">返单开单人</param>
        /// <returns></returns>
        public DataSet GetInnerData(int inType, DateTime inStartTime, DateTime inEndTime, int inUserId,int inFromID)
        {
            Database db = null;
            DataSet set = new DataSet();
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string strSQL = "";
                #region
                //                if (inFromID == 0)//无开单人
//                {
//                    if (inUserId == 0)//无责任人
//                    {
//                        if (inType == 0)//统计规则日
//                        {
//                            #region//Old
//                            //strSQL = "select isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay,sum(areas*2.54*2.54/10000) as area  from  " +
//                            //          "(select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, " +
//                            //          "CONVERT(varchar(12) ,InnerReturnOrder_BK.CreateTime , 23) as CreateDay  " +
//                            //          "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK  " +
//                            //          "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId  " +
//                            //          "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //          "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //          ")as ta left join users on users.userID=ta.ReturnUid " +
//                            //          "group by users.UserName,ReturnUid,CreateDay order by CreateDay ";
//                            //strSQL = "select UserName,ReturnUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select ReturnUserId,PhotoAreas,CONVERT(varchar(12) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime +
//                            //         "')) as ta,users where users.userID=ta.ReturnUserId  " +
//                            //         " group by CreateDay,ReturnUserId,UserName";
//                            #endregion

//                            strSQL =
//                                " select  PhotoAreas,CreateTime,CONVERT(varchar(12) ,CreateTime, 23)  CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
//                                " select PhotoAreas,CreateDay as CreateTime,CreateUserId,users.UserName as DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
//                                " select ReturnUserId,CreateUserId,PhotoAreas,CreateTime as CreateDay ,PhotoId,orderInfo_BK.OrderProductId,OrderProductName,CtmName,NewInnerReturn_BK.Remark " +
//                                " from dbo.NewInnerReturn_BK ,dbo.orderInfo_BK " +
//                                " where NewInnerReturn_BK.CreateTime between convert(datetime,'" + inStartTime +
//                                "') and  convert(datetime,'" + inEndTime + "')  " +
//                                // " and CreateUserId=32 " +
//                                " and NewInnerReturn_BK.OrderproductID=orderInfo_BK.OrderProductId " +
//                                " )as ta left join users on ta.ReturnUserId=users.userID   " +
//                                " )as tb left join users on tb.CreateUserId=users.userID ";
//                                //" group by users.UserName,CreateTime,DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark ";
//                               //" group by CreateDay,DutyName,users.UserName";
//                        }
//                        else if (inType == 1)//统计规则月
//                        {
//                            #region
//                            //strSQL = "select sum(areas*2.54*2.54/10000) as area ,isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay from (  " +
//                            //         "select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, users.UserName," +
//                            //         "CONVERT(varchar(7) ,InnerReturnOrder_BK.CreateTime , 23 ) as CreateDay " +
//                            //         "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK,dbo.users " +
//                            //         "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId  " +
//                            //         "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //    //"--and users.userID=ReturnUid  " +
//                            //         "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //         ") as ta left join users on users.userID=ta.ReturnUid  " +
//                            //         "group by users.UserName,ReturnUid,CreateDay order by CreateDay";
//                            //strSQL = "select UserName,ReturnUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select ReturnUserId,PhotoAreas,CONVERT(varchar(7) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime +
//                            //         "')) as ta,users where users.userID=ta.ReturnUserId  " +
//                            //         " group by CreateDay,ReturnUserId,UserName";
//#endregion

//                            strSQL =
//                                " select   PhotoAreas,CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName from ( " +
//                                " select PhotoAreas,CreateDay,CreateUserId,users.UserName as DutyName from (select ReturnUserId,CreateUserId,PhotoAreas,CONVERT(varchar(7),CreateTime,23) as CreateDay " +
//                                " from dbo.NewInnerReturn_BK " +
//                                " where CreateTime between convert(datetime,'" + inStartTime +"') and  convert(datetime,'" + inEndTime + "') " +
//                                " )as ta left join users on ta.ReturnUserId=users.userID " +
//                                " )as tb left join users on tb.CreateUserId=users.userID";
//                            //" group by CreateDay,DutyName,users.UserName";
//                        }
//                        else
//                        {
                            
//                        }
//                    }
//                    else //有责任人
//                    {
//                        if (inType == 0)//统计规则日
//                        {
//                            #region
//                            //strSQL = "select isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay,sum(areas*2.54*2.54/10000) as area  from  " +
//                            //         "(select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, " +
//                            //         "CONVERT(varchar(12) ,InnerReturnOrder_BK.CreateTime ,23 ) as CreateDay  " +
//                            //         "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK " +
//                            //         "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId " +
//                            //         "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId and " +
//                            //         "InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //         " and  ReturnUid=" + inUserId +
//                            //         ")as ta left join users on users.userID=ta.ReturnUid " +
//                            //         "group by users.UserName,ReturnUid,CreateDay order by CreateDay";
//                            //strSQL = "select UserName,ReturnUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select ReturnUserId,PhotoAreas,CONVERT(varchar(12) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime + "') and ReturnUserId=" + inUserId +
//                            //         ") as ta,users where users.userID=ta.ReturnUserId  " +
//                            //         " group by CreateDay,ReturnUserId,UserName";
//                            #endregion

//                            strSQL =
//                                " select  PhotoAreas,CreateTime,CONVERT(varchar(12) ,CreateTime, 23)  CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
//                                " select PhotoAreas,CreateDay as CreateTime,CreateUserId,users.UserName as DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
//                                " select ReturnUserId,CreateUserId,PhotoAreas,CreateTime as CreateDay ,PhotoId,orderInfo_BK.OrderProductId,OrderProductName,CtmName,NewInnerReturn_BK.Remark " +
//                                " from dbo.NewInnerReturn_BK ,dbo.orderInfo_BK " +
//                                " where NewInnerReturn_BK.CreateTime between convert(datetime,'" + inStartTime +
//                                "') and  convert(datetime,'" + inEndTime + "')  " +
//                                " and ReturnUserId= " +inUserId+
//                                " and NewInnerReturn_BK.OrderproductID=orderInfo_BK.OrderProductId " +
//                                " )as ta left join users on ta.ReturnUserId=users.userID   " +
//                                " )as tb left join users on tb.CreateUserId=users.userID ";
//                                //" group by CreateDay,DutyName,users.UserName";

//                        }
//                        else if (inType == 1)//统计规则日月
//                        {
//                            #region
//                            //strSQL = "select sum(areas*2.54*2.54/10000) as area ,isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay from ( " +
//                            //        "select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid,UserName, " +
//                            //        "CONVERT(varchar(7) ,InnerReturnOrder_BK.CreateTime , 23) as CreateDay " +
//                            //        "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK,dbo.users " +
//                            //        "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId " +
//                            //        "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //        "and users.userID=ReturnUid  " +
//                            //        "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //        " and  ReturnUid=" + inUserId +
//                            //        ") as ta  " +
//                            //        "group by users.UserName,ReturnUid,CreateDay order by CreateDay";
//                            //strSQL = "select UserName,ReturnUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select ReturnUserId,PhotoAreas,CONVERT(varchar(7) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime + "') and ReturnUserId=" + inUserId +
//                            //         " )as ta,users where users.userID=ta.ReturnUserId  " +
//                            //         " group by CreateDay,ReturnUserId,UserName";
//                            #endregion

//                            strSQL =
//                                " select  PhotoAreas,CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName from ( " +
//                                " select PhotoAreas,CreateDay,CreateUserId,users.UserName as DutyName from (select ReturnUserId,CreateUserId,PhotoAreas,CONVERT(varchar(7),CreateTime,23) as CreateDay " +
//                                " and ReturnUserId=" + inUserId +
//                                " from dbo.NewInnerReturn_BK " +
//                                " where CreateTime between convert(datetime,'" + inStartTime +
//                                "') and  convert(datetime,'" + inEndTime + "') " +
//                                " )as ta left join users on ta.ReturnUserId=users.userID " +
//                                " )as tb left join users on tb.CreateUserId=users.userID";
//                               //" group by CreateDay,DutyName,users.UserName";
//                        }
//                        else
//                        {
                          
//                        }

//                    }
//                }
//                else//有开单人
//                {
//                    if (inUserId == 0)//无责任人
//                    {
//                        if (inType == 0)//统计规则日
//                        {
//                            #region
//                            //strSQL = "select isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay,sum(areas*2.54*2.54/10000) as area  from  " +
//                            //          "(select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, " +
//                            //          "CONVERT(varchar(12) ,InnerReturnOrder_BK.CreateTime , 23) as CreateDay  " +
//                            //          "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK  " +
//                            //          "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId  " +
//                            //          "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //          "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //          ")as ta left join users on users.userID=ta.ReturnUid " +
//                            //          "group by users.UserName,ReturnUid,CreateDay order by CreateDay ";
//                            //strSQL = "select UserName,CreateUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select CreateUserId,PhotoAreas,CONVERT(varchar(12) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime +
//                            //         "')) as ta,users where users.userID=ta.CreateUserId  ";
//                            //         //" group by CreateDay,CreateUserId,UserName";
//                            #endregion
//                            strSQL =
//                               " select sum(PhotoAreas) as PhotoAreas,CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName from ( " +
//                               " select PhotoAreas,CreateDay,CreateUserId,users.UserName as DutyName from (select ReturnUserId,CreateUserId,PhotoAreas,CONVERT(varchar(12),CreateTime,23) as CreateDay " +
//                               " and  CreateUserId=" + inFromID +
//                               " from dbo.NewInnerReturn_BK " +
//                               " where CreateTime between convert(datetime,'" + inStartTime +
//                               "') and  convert(datetime,'" + inEndTime + "') " +
//                               " )as ta left join users on ta.ReturnUserId=users.userID " +
//                               " )as tb left join users on tb.CreateUserId=users.userID" +
//                               " group by CreateDay,DutyName,users.UserName";

//                        }
//                        else if (inType == 1)//统计规则月
//                        {
//                            #region
//                            //strSQL = "select sum(areas*2.54*2.54/10000) as area ,isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay from (  " +
//                            //         "select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, users.UserName," +
//                            //         "CONVERT(varchar(7) ,InnerReturnOrder_BK.CreateTime , 23 ) as CreateDay " +
//                            //         "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK,dbo.users " +
//                            //         "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId  " +
//                            //         "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //    //"--and users.userID=ReturnUid  " +
//                            //         "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //         ") as ta left join users on users.userID=ta.ReturnUid  " +
//                            //         "group by users.UserName,ReturnUid,CreateDay order by CreateDay";
//                            //strSQL = "select UserName,CreateUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select CreateUserId,PhotoAreas,CONVERT(varchar(7) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime +
//                            //         "')) as ta,users where users.userID=ta.CreateUserId  " +
//                            //         " group by CreateDay,CreateUserId,UserName";
//                            #endregion
//                            strSQL =
//                              " select  as PhotoAreas,CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName from ( " +
//                              " select PhotoAreas,CreateDay,CreateUserId,users.UserName as DutyName from (select ReturnUserId,CreateUserId,PhotoAreas,CONVERT(varchar(7),CreateTime,23) as CreateDay " +
//                              " and  CreateUserId=" + inFromID +
//                              " from dbo.NewInnerReturn_BK " +
//                              " where CreateTime between convert(datetime,'" + inStartTime +
//                              "') and  convert(datetime,'" + inEndTime + "') " +
//                              " )as ta left join users on ta.ReturnUserId=users.userID " +
//                              " )as tb left join users on tb.CreateUserId=users.userID" +
//                              " group by CreateDay,DutyName,users.UserName";
//                        }
//                        else
//                        {
                        
//                        }
//                    }
//                    else //有责任人
//                    {
//                        if (inType == 0)//统计规则日
//                        {
//                            #region
//                            //strSQL = "select isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay,sum(areas*2.54*2.54/10000) as area  from  " +
//                            //         "(select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, " +
//                            //         "CONVERT(varchar(12) ,InnerReturnOrder_BK.CreateTime ,23 ) as CreateDay  " +
//                            //         "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK " +
//                            //         "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId " +
//                            //         "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId and " +
//                            //         "InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //         " and  ReturnUid=" + inUserId +
//                            //         ")as ta left join users on users.userID=ta.ReturnUid " +
//                            //         "group by users.UserName,ReturnUid,CreateDay order by CreateDay";
//                            //strSQL = "select UserName,ReturnUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select ReturnUserId,PhotoAreas,CONVERT(varchar(12) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime + "') and ReturnUserId=" + inUserId +
//                            //         ") as ta,users where users.userID=ta.ReturnUserId  " +
//                            //         " group by CreateDay,ReturnUserId,UserName";
//                            #endregion
//                            strSQL =
//                              " select sum(PhotoAreas) as PhotoAreas,CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName from ( " +
//                              " select PhotoAreas,CreateDay,CreateUserId,users.UserName as DutyName from (select ReturnUserId,CreateUserId,PhotoAreas,CONVERT(varchar(12),CreateTime,23) as CreateDay " +
//                              " and  CreateUserId=" + inFromID +
//                              " and ReturnUserId=" + inUserId +
//                              " from dbo.NewInnerReturn_BK " +
//                              " where CreateTime between convert(datetime,'" + inStartTime +
//                              "') and  convert(datetime,'" + inEndTime + "') " +
//                              " )as ta left join users on ta.ReturnUserId=users.userID " +
//                              " )as tb left join users on tb.CreateUserId=users.userID" +
//                              " group by CreateDay,DutyName,users.UserName";

//                        }
//                        else if (inType == 1)//统计规则月
//                        {
//                            #region
//                            //strSQL = "select sum(areas*2.54*2.54/10000) as area ,isnull(users.UserName,'未指定') as UserName,ReturnUid,CreateDay from ( " +
//                            //        "select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid,UserName, " +
//                            //        "CONVERT(varchar(7) ,InnerReturnOrder_BK.CreateTime , 23) as CreateDay " +
//                            //        "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK,dbo.users " +
//                            //        "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId " +
//                            //        "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //        "and users.userID=ReturnUid  " +
//                            //        "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //        " and  ReturnUid=" + inUserId +
//                            //        ") as ta  " +
//                            //        "group by users.UserName,ReturnUid,CreateDay order by CreateDay";
//                            //strSQL = "select UserName,ReturnUserId,sum(PhotoAreas) as area,CreateDay from  " +
//                            //         " (select ReturnUserId,PhotoAreas,CONVERT(varchar(7) ,CreateTime, 23) as CreateDay  " +
//                            //         " from NewInnerReturn_BK where CreateTime between convert(datetime,'" + inStartTime +
//                            //         "') and  convert(datetime,'" + inEndTime + "') and ReturnUserId=" + inUserId +
//                            //         " )as ta,users where users.userID=ta.ReturnUserId  " +
//                            //         " group by CreateDay,ReturnUserId,UserName";
//                            #endregion
//                            strSQL =
//                             " select sum(PhotoAreas) as PhotoAreas,CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName from ( " +
//                             " select PhotoAreas,CreateDay,CreateUserId,users.UserName as DutyName from (select ReturnUserId,CreateUserId,PhotoAreas,CONVERT(varchar(7),CreateTime,23) as CreateDay " +
//                             " and  CreateUserId=" + inFromID +
//                             " and ReturnUserId=" + inUserId +
//                             " from dbo.NewInnerReturn_BK " +
//                             " where CreateTime between convert(datetime,'" + inStartTime +
//                             "') and  convert(datetime,'" + inEndTime + "') " +
//                             " )as ta left join users on ta.ReturnUserId=users.userID " +
//                             " )as tb left join users on tb.CreateUserId=users.userID" +
//                             " group by CreateDay,DutyName,users.UserName";
//                        }
//                        else
//                        {
//                            //strSQL = "select sum(areas*2.54*2.54/10000) as area ,isnull(UserName,'未指定') as UserName,ReturnUid, from ( " +
//                            //                               "select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid " +

//                            //                               "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK " +
//                            //                               "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId " +
//                            //                               "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId  " +
//                            //                               "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
//                            //                               " and  ReturnUid=" + inUserId +
//                            //                               ") as ta left join users on users.userID=ta.ReturnUid " +
//                            //                               "group by UserName,ReturnUid,CreateDay ";
//                        }

//                    }
                //                }
                #endregion
                if(inUserId==0&&inFromID==0)
                {
                    strSQL =
                        " select  PhotoAreas,CreateTime,CONVERT(varchar(12) ,CreateTime, 23)  CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                        " select PhotoAreas,CreateDay as CreateTime,CreateUserId,users.UserName as DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                        " select ReturnUserId,CreateUserId,PhotoAreas,CreateTime as CreateDay ,PhotoId,orderInfo_BK.OrderProductId,OrderProductName,CtmName,NewInnerReturn_BK.Remark " +
                        " from dbo.NewInnerReturn_BK ,dbo.orderInfo_BK " +
                        " where NewInnerReturn_BK.CreateTime between convert(datetime,'" + inStartTime +
                        "') and  convert(datetime,'" + inEndTime + "')  " +
                        // " and CreateUserId=32 " +
                        " and NewInnerReturn_BK.OrderproductID=orderInfo_BK.OrderProductId " +
                        " )as ta left join users on ta.ReturnUserId=users.userID   " +
                        " )as tb left join users on tb.CreateUserId=users.userID ";
                }else if(inUserId!=0&&inFromID==0)
                {
                    strSQL =
                        " select  PhotoAreas,CreateTime,CONVERT(varchar(12) ,CreateTime, 23)  CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                        " select PhotoAreas,CreateDay as CreateTime,CreateUserId,users.UserName as DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                        " select ReturnUserId,CreateUserId,PhotoAreas,CreateTime as CreateDay ,PhotoId,orderInfo_BK.OrderProductId,OrderProductName,CtmName,NewInnerReturn_BK.Remark " +
                        " from dbo.NewInnerReturn_BK ,dbo.orderInfo_BK " +
                        " where NewInnerReturn_BK.CreateTime between convert(datetime,'" + inStartTime +
                        "') and  convert(datetime,'" + inEndTime + "')  " +
                        " and ReturnUserId=" +inUserId+
                        " and NewInnerReturn_BK.OrderproductID=orderInfo_BK.OrderProductId " +
                        " )as ta left join users on ta.ReturnUserId=users.userID   " +
                        " )as tb left join users on tb.CreateUserId=users.userID ";
                }
                else if (inUserId==0&&inFromID!=0)
                {
                    strSQL =
                       " select  PhotoAreas,CreateTime,CONVERT(varchar(12) ,CreateTime, 23)  CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                       " select PhotoAreas,CreateDay as CreateTime,CreateUserId,users.UserName as DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                       " select ReturnUserId,CreateUserId,PhotoAreas,CreateTime as CreateDay ,PhotoId,orderInfo_BK.OrderProductId,OrderProductName,CtmName,NewInnerReturn_BK.Remark " +
                       " from dbo.NewInnerReturn_BK ,dbo.orderInfo_BK " +
                       " where NewInnerReturn_BK.CreateTime between convert(datetime,'" + inStartTime +
                       "') and  convert(datetime,'" + inEndTime + "')  " +
                      
                       " and CreateUserId=" + inFromID +
                       " and NewInnerReturn_BK.OrderproductID=orderInfo_BK.OrderProductId " +
                       " )as ta left join users on ta.ReturnUserId=users.userID   " +
                       " )as tb left join users on tb.CreateUserId=users.userID ";
                }
                else
                {
                    strSQL =
                      " select  PhotoAreas,CreateTime,CONVERT(varchar(12) ,CreateTime, 23)  CreateDay,isnull(DutyName,'Nobody') as DutyName ,users.UserName as CreateName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                      " select PhotoAreas,CreateDay as CreateTime,CreateUserId,users.UserName as DutyName,PhotoId,OrderProductId,OrderProductName,CtmName,Remark  from ( " +
                      " select ReturnUserId,CreateUserId,PhotoAreas,CreateTime as CreateDay ,PhotoId,orderInfo_BK.OrderProductId,OrderProductName,CtmName,NewInnerReturn_BK.Remark " +
                      " from dbo.NewInnerReturn_BK ,dbo.orderInfo_BK " +
                      " where NewInnerReturn_BK.CreateTime between convert(datetime,'" + inStartTime +
                      "') and  convert(datetime,'" + inEndTime + "')  " +
                      " and ReturnUserId=" + inUserId +
                      " and CreateUserId=" + inFromID +
                      " and NewInnerReturn_BK.OrderproductID=orderInfo_BK.OrderProductId " +
                      " )as ta left join users on ta.ReturnUserId=users.userID   " +
                      " )as tb left join users on tb.CreateUserId=users.userID ";
                }

                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                set = db.ExecuteDataSet(dbCommand);


            }
            catch
            { }
            return set;
        }
        public DataSet GetInnerData(DateTime inStartTime, DateTime inEndTime, int inUserId)
        {
            Database db = null;
            DataSet set = new DataSet();
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string strSQL = "";
                if (inUserId == 0)
                {

                    strSQL = "select area,isnull(UserName,' 未指定')  as UserName,ReturnUid from (select sum(areas*2.54*2.54/10000) as area ,ReturnUid " +
    "from (select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, " +
    "CONVERT(varchar(12) ,InnerReturnOrder_BK.CreateTime , 112 ) as CreateDay  " +
    "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK " +
    "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId  " +
                                   "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +

    "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId) as ta " +
    "group by ReturnUid) as tb  left join dbo.users on userID=ReturnUid ";
                }
                else
                {
                    strSQL = "select area,isnull(UserName,' 未指定') as UserName,ReturnUid from (select sum(areas*2.54*2.54/10000) as area ,ReturnUid " +
                   "from (select photoInfo_BK.Height*photoInfo_BK.Width as areas,ReturnUid, " +
                   "CONVERT(varchar(12) ,InnerReturnOrder_BK.CreateTime , 112 ) as CreateDay  " +
                   "from dbo.InnerReturnOrder_BK,dbo.orderInfo_BK ,dbo.photoInfo_BK " +
                   "where InnerReturnOrder_BK.OrderProductId=orderInfo_BK.ParentProductId  " +
                                                  "and InnerReturnOrder_BK.CreateTime between convert(datetime,'" + inStartTime + "') and  convert(datetime,'" + inEndTime + "') " +
                                                      " and  ReturnUid=" + inUserId +
                   "and orderInfo_BK.OrderProductId=dbo.photoInfo_BK.OrderProductId and ) as ta " +
                   "group by ReturnUid) as tb  left join dbo.users on userID=ReturnUid ";
                }


                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                set = db.ExecuteDataSet(dbCommand);


            }
            catch
            { }
            return set;
        }
    }
}
